Oracle中的Over()函数与ROLLUP的用法

2022-12-25 05:54| 来源: 网络整理| 查看: 265

Oracle中的Over()函数与ROLLUP的用法 Over()函数：

①先是简单的：用来计算两数相除之中分母的计算

SELECT supplierName as supplierName, TO_CHAR(ROUND(DECODE(SUM(amount) OVER(), 0,0, (amount / SUM(amount) OVER()))*100,2),'FM990.00') || '%' as percentage FROM XXX WHERE XXX

此处OVER()函数的作用意在优先算出分母SUM(amount)的合计，这样可以省去一层嵌套关系，在一层内直接得出结果。

OVER()中还可加“partition by”，其中“partition by”官方点的说法叫做"分区"，其实就是统计的范围条件。

注：此sql语句结尾处不要加“order by”，应为使用的分析函数的(partition by XXX order by sal)里已经有排序的语句了，如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费解了。

②rank()结合over()

rank函数是分级函数，这个函数必须与 over 函数使用，否则会报一个"缺少窗口函数的错"。我测试sql如下：

select a.empno as 员工编号, 　　a.sal as 薪资, 　　a.job as 岗位, 　　rank() OVER(partition by a.job ORDER BY a.sal desc) as 岗位薪资等级　　from scott.emp a; ROLLUP：

ROLLUP分组函数可以理解为Group By分组函数封装后的精简用法，这里直接给出ROLLUP的求合计行代码。

SELECT DECODE( grouping ( city ), 1, '12', to_char(MAX( nums )) ) id, DECODE( grouping ( city ), 1, '合计', city ) city, SUM( roomNight ) roomNight, DECODE( grouping ( city ), 1, '100', TO_CHAR(MAX( roomNightPercent ),'fm990.00') ) || '%' roomNightPercent, TO_CHAR(SUM( price ),'FM9999999990.00') price, DECODE( grouping ( city ), 1, '100', TO_CHAR(MAX( pricePercent ),'fm990.00') ) || '%' salePercent, DECODE(SUM( refundRoomNights ),0,0,-SUM( refundRoomNights )) refundRoomNight, TO_CHAR(DECODE(SUM( refundAmount ),0,0,-SUM( refundAmount )),'FM9999999990.00') refundAmount, DECODE( grouping ( city ), 1, '100', TO_CHAR(MAX( refundAmountPercent ),'fm990.00') ) || '%' refundAmountPercent, TO_CHAR(ROUND( DECODE( SUM( roomNight ), 0, 0, SUM( price ) / SUM( roomNight ) ), 2 ),'FM990.00') avgPrice FROM ( SELECT MAX( num ) nums, DECODE( MAX( num ), 11, '其他', city ) city, SUM( roomNight ) roomNight, DECODE(SUM( roomNight ), 0, 0,TO_CHAR( ROUND( SUM( nvl(roomNight,0) ) / SUM( SUM( roomNight ) ) over ( ) * 100, 2 ), 'Fm990.00' )) AS roomNightPercent, SUM( price ) price, DECODE(SUM( price ), 0, 0,TO_CHAR( ROUND( SUM( price ) / SUM( SUM( price ) ) over ( ) * 100, 2 ), 'Fm990.00' )) AS pricePercent, SUM( refundRoomNights ) refundRoomNights, SUM( refundAmount ) refundAmount, DECODE(SUM( refundAmount ), 0, 0,TO_CHAR( ROUND( SUM( refundAmount ) / SUM( SUM( refundAmount ) ) over ( ) * 100, 2 ), 'Fm990.00' )) AS refundAmountPercent, SUM( avgAmount ) avgAmount FROM ( SELECT CASE WHEN ROWNUM ;= 10 THEN to_char( ROWNUM ) ELSE '11' END AS num, MAX( city ) city, SUM( roomNight ) roomNight, SUM( price ) price, SUM( refundRoomNights ) refundRoomNights, SUM( refundAmount ) refundAmount, SUM( avgAmount ) avgAmount FROM ( SELECT city city, SUM( roomNight ) roomNight, SUM( price ) price, SUM( refundRoomNights ) refundRoomNights, SUM( refundAmount ) refundAmount, SUM( avgAmount ) avgAmount FROM ( SELECT CITY city, SUM( ROOM_NIGHTS ) roomNight, SUM( SETTLE_PRICE_TOTAL ) price, 0 refundRoomNights, 0 refundAmount, 0 avgAmount FROM T_HOTEL_SALE WHERE SUPPLIER != 'GRCB' AND SUBSTR(BOOK_DATE, 0, 10) BETWEEN #{startDate} AND #{endDate} AND MEMBERNAME = #{customer} GROUP BY CITY UNION ALL SELECT CITY city, 0 roomNight, 0 price, SUM( ROOM_NIGHTS ) refundRoomNights, SUM( SETTLE_PRICE_TOTAL ) refundAmount, 0 avgAmount FROM T_HOTEL_SALE WHERE SUPPLIER != 'GRCB' AND STATUS = '9' AND SUBSTR(BOOK_DATE, 0, 10) BETWEEN #{startDate} AND #{endDate} AND MEMBERNAME = #{customer} GROUP BY CITY ) GROUP BY city ORDER BY price DESC ) GROUP BY CASE WHEN ROWNUM ;= 10 THEN to_char( ROWNUM ) ELSE '11' END ORDER BY TO_NUMBER( num ) ) GROUP BY city ORDER BY TO_NUMBER( nums ) ) GROUP BY Rollup ( city, nums ) HAVING grouping ( city ) = 1 OR nums IS NOT NULL ORDER BY TO_NUMBER( nums )

这里sql是以 city, nums（城市，行号）来进行分组再求和的，

需要注意的是如果sum()搭配Rollup使用时要注意这个条件：

HAVING grouping ( city ) = 1 OR nums IS NOT NULL

否则会导致统计数据翻倍

SELECT DECODE( grouping ( city ), 1, '合计', city ) city, ......

SELECT中再使用DECODE函数进行筛选赋值

grouping ( city ) 如果显示“1”表示GROUPING函数对应的列是由于ROLLUP函数所产生的空值对应的信息，即对此列进行汇总计算后的结果。如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。

ROLLUP在数据统计和报表生成过程中带来极大的便利，而且效率比起来Group By + Union组合方法效率高得多。这也体现了Oracle在SQL统计分析上人性化、自动化、高效率的特点。

【本文地址】

公司简介

联系我们